CREATE PROCEDURE PersonRole_FindAll
AS
BEGIN
    Select 
    PersonRoleID,
    ApplicationID,
    PersonID,
    UserName,
    Role
    from PersonRole
END
GO

CREATE PROCEDURE PersonRole_FindById
@PersonRoleID int
AS
BEGIN
    Select * from PersonRole where PersonRoleID = @PersonRoleID
END
GO

CREATE PROCEDURE PersonRole_FindByName
@name nvarchar(50)
AS
BEGIN
    Select * from PersonRole where PersonRoleID = @name
END
GO

CREATE PROCEDURE PersonRole_Insert
        @PersonRoleID int ,
        @ApplicationID int ,
        @PersonID int ,
        @UserName nvarchar (100),
        @Role nvarchar (100)
AS
BEGIN
INSERT INTO PersonRole
(
        PersonRoleID,
        ApplicationID,
        PersonID,
        UserName,
        Role
)
VALUES
(
        @PersonRoleID,
        @ApplicationID,
        @PersonID,
        @UserName,
        @Role
)
select @PersonRoleID = @@IDENTITY
END
GO

CREATE PROCEDURE PersonRole_Update
        @PersonRoleID int ,
        @ApplicationID int ,
        @PersonID int ,
        @UserName nvarchar (100),  
        @Role nvarchar (100),
AS
BEGIN
UPDATE  PersonRole SET
    ApplicationID = @ApplicationID,
    PersonID = @PersonID,
    UserName = @UserName,
    Role = @Role
    WHERE PersonRoleID = @PersonRoleID
END
GO

CREATE PROCEDURE PersonRole_Delete
    @PersonRoleID int
AS
BEGIN
    DELETE FROM PersonRole where PersonRoleID = @PersonRoleID
END
GO

CREATE PROCEDURE PersonRole_Paging
    @PageSize int, 
    @PageNumber int
    AS 
    declare @RowStart int
    declare @RowEnd int
    if @PageNumber > 0 
    BEGIN	
    set @PageNumber = @PageNumber - 1
    set @RowStart = @PageSize * @PageNumber + 1;
    set @RowEnd = @RowStart  + @PageSize - 1;
    SELECT TotalRowCount = COUNT(PersonRoleID) FROM PersonRole;
    WITH MyTable as 
    (
    SELECT * ,  ROW_NUMBER() OVER (order by SortIndex) as RowNumber From  PersonRole
    )
    SELECT * from MyTable where RowNumber >= @RowStart and RowNumber <= @RowEnd
END
GO

CREATE proc PersonRole_SetActive
@PersonRoleID int
AS
declare @Type bit
SELECT @Type =  IsActive from PersonRole where PersonRoleID = @PersonRoleID
BEGIN
IF(@Type = 1)
Update PersonRole set IsActive = 0 where PersonRoleID = @PersonRoleID
else
Update PersonRole set IsActive = 1 where PersonRoleID = @PersonRoleID
END
GO

CREATE proc PersonRole_SetSortIndex
@PersonRoleID int, 
@Type int
AS
declare @CurrentSort int
select @CurrentSort = SortIndex from  PersonRole where PersonRoleID = @PersonRoleID
BEGIN
IF(@Type = 1)
Update PersonRole set SortIndex =  @CurrentSort + 1 where PersonRoleID = @PersonRoleID
else
Update PersonRole set SortIndex =  @CurrentSort - 1 where PersonRoleID = @PersonRoleID
END
GO

CREATE PROCEDURE PersonRole_RemoveSelectedItem
    @PersonRoleID int
AS
BEGIN
    UPDATE PersonRole SET IsDeleted = 1 WHERE PersonRoleID = @PersonRoleID
END
GO

CREATE PROCEDURE PersonRole_RestoreSelectedItem
    @PersonRoleID int
AS
BEGIN
    UPDATE PersonRole SET IsDeleted = 0 WHERE PersonRoleID = @PersonRoleID
END
GO

CREATE PROCEDURE PersonRole_RemoveAll
    @PersonRoleID int
AS
BEGIN
    UPDATE PersonRole SET IsDeleted = 1
END
GO

CREATE PROCEDURE PersonRole_RestoreAll
    @PersonRoleID int
AS
BEGIN
    UPDATE PersonRole SET IsDeleted = 0
END
GO

